﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Linq;
using CNative.Utilities;
using System.Data.Common;

namespace CNative.Dapper.Utils
{
    internal class SqliteProvider : BaseProvider
    {
        public SqliteProvider(IDbHelper _db) : base(_db)
        {
        }
        #region 关键字
        /// <summary>
        /// 数据库提供程序名字
        /// </summary>
        public override string ProviderName
        {
            get
            {
                return "System.Data.SQLite";
            }
        }
        public override string ProviderNameFactory
        {
            get
            {
                return "System.Data.SQLite.SQLiteFactory";
            }
        }
        /// <summary>
        /// 数据库类型
        /// </summary>
        public override DatabaseType DBType { get { return DatabaseType.Sqlite; } }
        /// <summary>
        /// 参数关键字 @ or :
        /// </summary>
        public override string ParamKeyword
        {
            get
            {
                return "@";
            }
        }
        /// <summary>
        /// 关键字前缀 [
        /// </summary>
        public override string SuffixLeft
        {
            get
            {
                return "[";
            }
        }
        /// <summary>
        /// 关键字后缀 ]
        /// </summary>
        public override string SuffixRigh
        {
            get
            {
                return "]";
            }
        }
        #endregion

        #region DbProviderFactory
        public override string GetSchemaTableName(Type tableType)
        {
            var tb = Funs.GetDbTableInfo(dbHelper, tableType);
            if (tb == null)
            {
                return "";
            }
            return tb.TableName;
        }
        #endregion

        #region GetDbParameter
        ///// <summary>
        ///// 获取DbParameter
        ///// </summary>
        ///// <param name="dbType">数据库类型</param>
        ///// <returns></returns>
        //public override IDataParameter GetDbParameter(string propName, object val, string paramSuffix = "exp_",
        //    DbTableInfo tb = null, ParameterDirection direction = ParameterDirection.Input, DbType valDBType = DbType.String)
        //{
        //    IDataParameter para = null;
        //    //--------------------------------------------------------------------------------------------------------------
        //    var frets = FillerParameter(propName, val, paramSuffix);
        //    if (frets.Item1)
        //    {
        //        return para;
        //    }
        //    var parameterName = frets.Item2;
        //    val = frets.Item3;
        //    //--------------------------------------------------------------------------------------------------------------
        //    #region Sqlite
        //    //if (direction == ParameterDirection.Output)
        //    //{
        //    //    para = new System.Data.SQLite.SQLiteParameter()
        //    //    {
        //    //        ParameterName = parameterName,
        //    //        Value = val,
        //    //        DbType = valDBType,
        //    //        Direction = direction
        //    //    };
        //    //    return para;
        //    //}

        //    para = new SQLiteParameter()
        //    {
        //        ParameterName = parameterName,
        //        Value = val,
        //        Direction = direction
        //    };
        //    if (tb != null && tb.TableInfo != null)
        //    {
        //        var dtinfo = tb.TableInfo.Find(f => f.Name.Equals(propName.Trim(), StringComparison.OrdinalIgnoreCase));
        //        if (dtinfo != null)
        //        {
        //            try
        //            {
        //                (para).DbType = SqlLiteTypeString2SqlType(dtinfo.Type);
        //                if (dtinfo.MaxLength > 0 && dtinfo.MaxLength < int.MaxValue)
        //                    (para as SQLiteParameter).Size = (int)dtinfo.MaxLength;
        //            }
        //            catch { }
        //        }
        //    }
        //    #endregion

        //    return para;
        //}
        //private System.Data.DbType SqlLiteTypeString2SqlType(string sqlTypeString)
        //{
        //    System.Data.DbType dbType = DbType.String; //默认为Object
        //    sqlTypeString = sqlTypeString.Trim().ToLower();
        //    #region switch (sqlTypeString)
        //    //switch (sqlTypeString)
        //    //{
        //    //    case "boolean": return DbType.Boolean;
        //    //    case "integer": return DbType.Int32;
        //    //    case "int2": return DbType.Byte;
        //    //    case "unsigned": return DbType.Decimal;
        //    //    case "int":
        //    //        dbType = DbType.Int32;
        //    //        break;
        //    //    case "int64":
        //    //        dbType = DbType.Int64;
        //    //        break;
        //    //    case "varchar":
        //    //        dbType = DbType.String;
        //    //        break;
        //    //    case "bit":
        //    //        dbType = DbType.Byte;
        //    //        break;
        //    //    case "datetime":
        //    //        dbType = DbType.DateTime;
        //    //        break;
        //    //    case "date":
        //    //        dbType = DbType.Date;
        //    //        break;
        //    //    case "time":
        //    //        dbType = DbType.Time;
        //    //        break;
        //    //    case "datetime2":
        //    //        dbType = DbType.DateTime2;
        //    //        break;
        //    //    case "datetimeoffset":
        //    //        dbType = DbType.DateTimeOffset;
        //    //        break;
        //    //    case "decimal":
        //    //        dbType = DbType.Decimal;
        //    //        break;
        //    //    case "udt":
        //    //        dbType = DbType.DateTimeOffset;
        //    //        break;
        //    //    case "float":
        //    //        dbType = DbType.Single;
        //    //        break;
        //    //    case "image":
        //    //        dbType = DbType.Binary;
        //    //        break;
        //    //    case "money":
        //    //        dbType = DbType.Currency;
        //    //        break;
        //    //    case "ntext":
        //    //        dbType = DbType.String;
        //    //        break;
        //    //    case "nvarchar":
        //    //        dbType = DbType.String;
        //    //        break;
        //    //    case "smalldatetime":
        //    //        dbType = DbType.Date;
        //    //        break;
        //    //    case "smallint":
        //    //        dbType = DbType.Int16;
        //    //        break;
        //    //    case "text":
        //    //        dbType = DbType.String;
        //    //        break;
        //    //    case "bigint":
        //    //        dbType = DbType.Int64;
        //    //        break;
        //    //    case "uniqueidentifier":
        //    //    case "guid":
        //    //        dbType = DbType.Guid;
        //    //        break;
        //    //    case "binary":
        //    //        dbType = DbType.Binary;
        //    //        break;
        //    //    case "xml":
        //    //        dbType = DbType.Xml;
        //    //        break;
        //    //    case "nvarchar(255)": return DbType.String;

        //    //    case "character(36)": return DbType.AnsiString;
        //    //}
        //    #endregion
        //    var parameterType = GetParameterTypeName(sqlTypeString);
        //    dbType = parameterType.ToEnum<DbType>();
        //    return dbType;
        //}
        #endregion
        #region MappingTypes
        /// <summary>
        /// 类型映射
        /// sqlTypeName，CsharpType，ParameterType
        /// </summary>
        public override List<Tuple<string, string, string>> MappingTypes
        {
            get
            {
                return new List<Tuple<string, string, string>>()
                    {
                        Tuple.Create("bit", "bool", "Byte"),
                        Tuple.Create("bigint", "long", "Int64"),
                        Tuple.Create("datetime", "DateTime", "Datetime"),
                        Tuple.Create("date", "DateTime", "Date"),
                        Tuple.Create("smalldatetime", "DateTime", "Date"),
                        Tuple.Create("time", "DateTime", "Time"),
                        Tuple.Create("timestamp", "string", "Timestamp"),
                        Tuple.Create("datetime2", "DateTime", "DateTime"),
                        Tuple.Create("datetimeoffset", "DateTime", "Timestamp"),
                        Tuple.Create("udt", "DateTime", "DateTimeOffset"),
                        Tuple.Create("decimal", "decimal", "Decimal"),
                        Tuple.Create("money", "double", "Currency"),
                        Tuple.Create("binary", "byte[]", "Binary"),
                        Tuple.Create("decimal", "decimal", "Decimal"),
                        Tuple.Create("float", "decimal", "Single"),
                        Tuple.Create("integer", "Int64", "Int64"),
                        Tuple.Create("smallint", "short", "Int16"),
                        Tuple.Create("image", "byte[]", "Binary"),
                        //Tuple.Create("real", "decimal", "Float"),
                        Tuple.Create("text", "string", "String"),
                        Tuple.Create("ntext", "string", "String"),
                        //Tuple.Create("longtext", "string", "LongText"),
                        Tuple.Create("char", "string", ",String"),
                        Tuple.Create("nchar", "string", ",String"),
                        Tuple.Create("varchar", "string", ",String"),
                        Tuple.Create("nvarchar", "string", ",String"),
                        Tuple.Create("nvarchar(255)", "string", ",String"),
                        Tuple.Create("character(36)", "string", ",AnsiString"),
                        Tuple.Create("guid", "Guid", ",Guid"),
                        Tuple.Create("uniqueidentifier", "Guid", ",Guid")
                    };
            }
        }

        /// <summary>
        /// 获取库类型
        /// </summary>
        /// <param name="csharpTypeName"></param>
        /// <returns></returns>
        public override string GetDbTypeName(string csharpTypeName)
        {
            if (csharpTypeName == Constants.ByteArrayType.Name)
                return "binary";
            csharpTypeName = CheckCsharpTypeName(csharpTypeName);
            var mappings = this.MappingTypes?.Where(it => it.Item2.Equals(csharpTypeName, StringComparison.CurrentCultureIgnoreCase)).ToList();
            if (mappings != null && mappings.Count > 0)
                return mappings.First().Item1;
            else
                return "varchar";
        }
        /// <summary>
        /// 获取sql Parameter Type
        /// </summary>
        /// <param name="dbTypeName"></param>
        /// <returns></returns>
        public override string GetParameterTypeName(string dbTypeName)
        {
            var mappings = this.MappingTypes?.Where(it => it.Item1.Equals(dbTypeName, StringComparison.CurrentCultureIgnoreCase));
            return !mappings.IsNullOrEmpty_() ? mappings.First().Item3 : "VarChar";
        }
        #endregion

        #region Sql Fun Templet
        /// <summary>
        /// SQL NVL() 从两个表达式返回一个非 null 值	函数
        /// </summary>
        /// <param name="check_expression">将被检查是否为 NULL的表达式。可以是任何类型的。</param>
        /// <param name="replacement_value">在 check_expression 为 NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。</param>
        /// <returns>如果 check_expression 不为 NULL，那么返回该表达式的值；否则返回 replacement_value</returns>
        public override string SQL_NVL(string check_expression, string replacement_value)
        {
            return $"IFNULL({check_expression},{replacement_value})";
        }
        /// <summary>
        /// 获取数据库时间函数
        /// </summary>
        public override string SqlDateNow
        {
            get
            {
                return "DATETIME('now') "; //"datetime(current_timestamp,'localtime')";
            }
        }
        /// <summary>
        /// 获取当前时间sql语句
        /// </summary>
        public override string FullSqlDateNow
        {
            get
            {
                return "select DATETIME('now') ";
            }
        }
        /// <summary>
        /// SQL UCASE() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数把字段的值转换为大写</returns>
        public override string SQL_UCASE(string column_name)
        {
            return $"UPPER({column_name})";
        }

        /// <summary>
        /// SQL LCASE() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数把字段的值转换为小写</returns>
        public override string SQL_LCASE(string column_name)
        {
            return $"LOWER({column_name})";
        }

        /// <summary>
        /// SQL LEFT(s,n) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>返回字符串 str 的前 length 个字符</returns>
        public override string SQL_LEFT(string str, int length)
        {
            return $"LEFT({str},{length})";
        }
        /// <summary>
        /// SQL RIGHT(s,n) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>返回字符串 str 的前 length 个字符</returns>
        public override string SQL_RIGHT(string str, int length)
        {
            return $"RIGHT({str},{length})";
        }
        /// <summary>
        /// SQL SUBSTR(s, start, length) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>从字符串 str 的 start 位置截取长度为 length 的子字符串</returns>
        public override string SQL_SUBSTR(string str, int start, int length)
        {
            return $"SUBSTR({str},{1 + start},{length})";
        }
        /// <summary>
        /// SQL TRIM(s) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>去掉字符串 str 开始和结尾处的空格</returns>
        public override string SQL_TRIM(string str)
        {
            return $"TRIM({str})";
        }
        /// <summary>
        /// SQL CAST() 转换数据类型	函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <param name="type">数据类型</param>
        /// <returns>转换数据类型</returns>
        public override string SQL_CAST(string column_name, string type)
        {
            return $"CAST({column_name} AS {type})";
        }


        public override string SQL_ToInt32(string column_name)
        {
            return SQL_CAST(column_name, "INTEGER");
        }

        public override string SQL_ToInt64(string column_name)
        {
            return SQL_CAST(column_name, "INTEGER");
        }
        public override string SQL_ToDouble(string column_name)
        {
            return SQL_CAST(column_name, "DECIMAL(18,4)");
        }
        /// <summary>转换高精度的十进制数（一般用于货币）</summary>
        /// <param name="column_name"></param>
        /// <param name="d">小数位数</param>
        /// <returns></returns>
        public override string SQL_ToDecimal(string column_name, int d)
        {
            if (d < 0 || d > 10) d = 4;
            return SQL_CAST(column_name, "DECIMAL(18, " + d + ")");
        }

        public override string SQL_ToBool(string column_name)
        {
            return SQL_CAST(column_name, "SIGNED");
        }

        public override string SQL_ToVarchar(string column_name)
        {
            return SQL_CAST(column_name, "TEXT");
        }

        public override string SQL_ToGuid(string column_name)
        {
            return SQL_CAST(column_name, "TEXT");
        }

        public override string SQL_ToDate(string column_name)
        {
            return string.Format(" DATETIME({0})", column_name);
        }

        public override string SQL_ToDateShort(string column_name)
        {
            return string.Format(" strftime('%Y-%m-%d', {0})", column_name);
        }

        public override string SQL_ToTime(string column_name)
        {
            return SQL_CAST(column_name, "TIME");
        }
        #endregion

        #region SqlTemplet
        /// <summary>
        /// 一般查询语句模板
        /// </summary>
        /// <param name="tbname"></param>
        /// <param name="fieldsstr"></param>
        /// <param name="orderbyStr"></param>
        /// <param name="top"></param>
        /// <returns></returns>
        public override string GetSelectSQL(String tbname, string fieldsstr, string orderbyStr, int top = 0)
        {
            var sqlMeta = @"SELECT " + fieldsstr + " FROM " + tbname + " WHERE {0} " + orderbyStr + (top > 0 ? " LIMIT 0, " + top + " " : "");
            return sqlMeta;
        }
        /// <summary>
        /// 分页查询语句模板
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="fieldsstr"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderbyStr"></param>
        /// <param name="page">当前页面</param>
        /// <param name="nums">每页记录数</param>
        /// <returns></returns>
        public override string GetSelectPageSQL(string tbName, string fieldsstr, string whereStr, string orderbyStr, int page = 1, int nums = 25)
        {
            if (page < 1) page = 1;
            if (nums < 1) nums = 10;
            var m = (page - 1) * nums;// + 1;
            var n = nums;// page * nums;

            var sqlMeta = $"SELECT {fieldsstr} FROM {tbName} WHERE {whereStr } {orderbyStr} limit {m},{n}";
            return sqlMeta;
        }
        /// <summary>
        /// MERGE 合并语句模板
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="tbname"></param>
        /// <returns></returns>
        public override string GetMergeSql(String tbname)
        {
            var sqlMeta = @"replace into " + tbname + " {1} {2};";

            return sqlMeta;
        }

        /// <summary>
        /// 更新语句模板
        /// "UPDATE " + (isMerge ? "" : fromStr) + " SET {0} " + (isMerge ? "" : " WHERE {1} ")
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="isMerge">是参与MERGE</param>
        /// <returns></returns>
        public override string GetUpdateSql(String fromStr, bool isMerge = false)
        {
            var sqlMeta = "UPDATE " + (isMerge ? "" : fromStr) + " SET {0} " + (isMerge ? "" : " WHERE {1} ");

            return sqlMeta;
        }
        /// <summary>
        /// 插入语句模板
        /// "INSERT " + (isMerge ? "" : " INTO " + fromStr) + " ({0}) VALUES ({1})"
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="isMerge">是参与MERGE</param>
        /// <returns></returns>
        public override string GetInsertSql(String fromStr, bool isMerge = false)
        {
            var sqlMeta = "INSERT " + (isMerge ? "" : " INTO " + fromStr) + " ({0}) VALUES ({1})";

            return sqlMeta;
        }
        /// <summary>
        /// 统计数量语句模板
        /// $"SELECT Count(0) FROM {fromStr} WHERE {0}";
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public override string GetCountSql(String fromStr, string fieldName = "")
        {
            var sqlMeta = "SELECT Count(" + (fieldName.IsNullOrEmpty() ? "0" : fieldName) + ") FROM " + fromStr + " WHERE {0}";

            return sqlMeta;
        }
        /// <summary>
        /// GetInsertSelectSql
        /// INSERT INTO " + insertTable + " ({0}) " + " SELECT {1} FROM " + selectTable + "  WHERE {2} 
        /// </summary>
        /// <param name="insertTable"></param>
        /// <param name="selectTable"></param>
        /// <param name="_dbType"></param>
        /// <returns></returns>
        public override string GetInsertSelectSql(String insertTable, String selectTable)
        {
            var sqlMeta = "INSERT INTO " + insertTable + " ({0}) " +
                            " SELECT {1} FROM " + selectTable + "  WHERE {2} ";

            return sqlMeta;
        }
        #endregion

        #region WithNextSequence
        /// <summary>
        /// 下个序列脚本
        /// </summary>
        /// <param name="sequenceName"></param>
        /// <returns></returns>
        public override string WithNextSequence(string sequenceName = null)
        {
            if (string.IsNullOrEmpty(sequenceName))
            {
                sequenceName = "default_seq";
            }
            return $"nextval('{sequenceName}')";
        }
        #endregion

        #region DML CodeFirst
        /// <summary>
        /// 通过连接字符串和表名获取数据库表的信息
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="DbName"></param>
        /// <returns></returns>
        public override SqlEntity GetDbTableInfoSQL(IDbHelper dbHelper, string tableName, string DbName)
        {
            var sqle = dbHelper.CreateSqlEntity();
            sqle.Sql = string.Format(GetColumnInfosByTableNameSql, "", tableName);
            return sqle;
        }
        /// <summary>
        /// 获取所有数据库
        /// </summary>
        public override string GetDataBaseSql
        {
            get
            {
                return ".database";
            }
        }
        /// <summary>
        /// 获取数据库中的所有表
        /// </summary>
        public override string GetTableInfoListSql
        {
            get
            {
                return @"{0}select name,'' Description from sqlite_master where type='table' and name<>'sqlite_sequence' order by name;";
            }
        }
        /// <summary>
        /// 获取数据库中的所有视图
        /// </summary>
        public override string GetViewInfoListSql
        {
            get
            {
                return @"{0}select name from sqlite_master where type='view'  order by name;";
            }
        }

        /// <summary>
        /// 得到一个表的所有列信息
        /// </summary>
        public override string GetColumnInfosByTableNameSql
        {
            get
            {
                return @"{0}PRAGMA table_info('{1}');";
            }
        }
        #endregion

        #region BulkCopyData
        /// <summary>  
        /// 批量插入功能  
        /// </summary>  
        public override bool BulkCopyData(DataTable dataTable, string destinationTableName = null, int? bulkCopyTimeout = null)
        {
            if (dataTable == null || dataTable.Rows.Count == 0) return false;
            if (string.IsNullOrEmpty(destinationTableName))
                destinationTableName = dataTable.TableName;
            dataTable.TableName = destinationTableName;

            dbHelper.BeginTransaction();

            var sqle = dbHelper.CreateSqlEntity();
            sqle.Sql = GenerateInserSql(sqle, dataTable);
            if (sqle.Sql.IsNullOrEmpty_())
                return false;

            Dictionary<string, object> valuePairs = new Dictionary<string, object>();
            var flag = true;
            var insertCount = 0;
            dataTable.Rows.CastToList<DataRow>().ForEach(row =>
              {
                  ProcessParameters(dataTable, row, valuePairs);
                  if (flag)
                  {
                      sqle.Parameter = valuePairs;
                         //valuePairs.ForEach(p => sqle.AddParameter(p.Value.ParameterName, p.Value.Value, p.Value.DbType, p.Value.Direction));
                         flag = false;
                  }
                  insertCount += dbHelper.Execute(sqle) ? 1 : 0;
              });
            dbHelper.Commit();
            return insertCount > 0;
        }

        /// <summary>
        /// 生成插入数据的sql语句。
        /// </summary>
        /// <param name="database"></param>
        /// <param name="command"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        protected virtual string GenerateInserSql(SqlEntity sqle, DataTable table)
        {
            var names = new StringBuilder();
            var values = new StringBuilder();
            var flag = true;
            table.Columns.CastToList<DataColumn>().ForEach(column =>
            {
                if (!flag)
                {
                    names.Append(",");
                    values.Append(",");
                }
                names.Append(FormatByQuote(column.ColumnName));
                values.AppendFormat("{0}{1}", ParamKeyword, column.ColumnName);
                flag = false;
            });
            return string.Format("INSERT INTO {0}({1}) VALUES ({2})", FormatByQuote(table.TableName), names, values);
        }

        private void ProcessParameters(DataTable dataTable, DataRow row, Dictionary<string, object> valuePairs)
        {
            for (var c = 0; c < dataTable.Columns.Count; c++)
            {
                valuePairs[dataTable.Columns[c].ColumnName] = row[c].IsNullOrDBNull_()?null:row[c];
                //DbParameter parameter;
                ////首次创建参数，是为了使用缓存
                //if (!valuePairs.TryGetValue(dataTable.Columns[c].ColumnName, out parameter))
                //{
                //    parameter = _dbProviderFactory.Value.CreateParameter();
                //    parameter.ParameterName = dataTable.Columns[c].ColumnName;

                //    valuePairs.Add(dataTable.Columns[c].ColumnName, parameter);
                //}
                //parameter.Value = row[c];
            }
        }
        #endregion

        #region DDL
        public override IDbFirst DbFirst { get { return new SqliteDbFirst(this.dbHelper.DBName); } }
        #region DDL sql
        protected override string CreateDataBaseSql
        {
            get
            {
                return "CREATE DATABASE {0}";
            }
        }
        protected override string AddPrimaryKeySql
        {
            get
            {
                throw new NotSupportedException();
            }
        }
        protected override string AddColumnToTableSql
        {
            get
            {
                return "ALTER TABLE {0} ADD COLUMN {1} {2}{3}";
            }
        }
        protected override string AlterColumnToTableSql
        {
            get
            {
                // return "ALTER TABLE {0} ALTER COLUMN {1} {2}{3} {4} {5} {6}";
                throw new NotSupportedException();
            }
        }
        protected override string BackupDataBaseSql
        {
            get
            {
                throw new NotSupportedException();
            }
        }
        protected override string CreateTableSql
        {
            get
            {
                return "CREATE TABLE {0}(\r\n{1} )";
            }
        }
        protected override string CreateTableColumn
        {
            get
            {
                return "{0} {1}{2} {3} {4} {5}";
            }
        }
        protected override string TruncateTableSql
        {
            get
            {
                return "DELETE FROM {0}";
            }
        }
        protected override string BackupTableSql
        {
            get
            {
                return " CREATE TABLE {0} AS SELECT * FROM {1} limit 0,{2}";
            }
        }
        protected override string DropTableSql
        {
            get
            {
                return "DROP TABLE {0}";
            }
        }
        protected override string DropColumnToTableSql
        {
            get
            {
                throw new NotSupportedException();
            }
        }
        protected override string DropConstraintSql
        {
            get
            {
                throw new NotSupportedException();
            }
        }
        protected override string RenameColumnSql
        {
            get
            {
                throw new NotSupportedException();
            }
        }

        protected override string AddColumnRemarkSql
        {
            get
            {
                throw new NotSupportedException();
            }
        }

        protected override string DeleteColumnRemarkSql
        {
            get
            {
                throw new NotSupportedException();
            }
        }

        protected override string IsAnyColumnRemarkSql
        {
            get
            {
                throw new NotSupportedException();
            }
        }

        protected override string AddTableRemarkSql
        {
            get
            {
                throw new NotSupportedException();
            }
        }

        protected override string DeleteTableRemarkSql
        {
            get
            {
                throw new NotSupportedException();
            }
        }

        protected override string IsAnyTableRemarkSql
        {
            get
            {
                throw new NotSupportedException();
            }
        }

        protected override string RenameTableSql
        {
            get
            {
                return "alter table  {0} rename to {1}";
            }
        }

        protected override string CreateIndexSql
        {
            get
            {
                return "CREATE {3} INDEX Index_{0}_{2} ON {0}({1})";
            }
        }
        protected override string AddDefaultValueSql
        {
            get
            {
                throw new NotSupportedException();
            }
        }
        protected override string IsAnyIndexSql
        {
            get
            {
                return "SELECT count(*) FROM sqlite_master WHERE name = '{0}'";
            }
        }
        #endregion

        #region Check
        protected override string CheckSystemTablePermissionsSql
        {
            get
            {
                return "select Name from sqlite_master limit 0,1";
            }
        }
        #endregion

        #region Scattered
        protected override string CreateTableNull
        {
            get
            {
                return "NULL";
            }
        }
        protected override string CreateTableNotNull
        {
            get
            {
                return "NOT NULL";
            }
        }
        protected override string CreateTablePirmaryKey
        {
            get
            {
                return "PRIMARY KEY";
            }
        }
        protected override string CreateTableIdentity
        {
            get
            {
                return "AUTOINCREMENT";
            }
        }
        #endregion

        #region Methods
        /// <summary>
        ///by current connection string
        /// </summary>
        /// <param name="databaseDirectory"></param>
        /// <returns></returns>
        public override bool CreateDatabase(string databaseName, string databaseDirectory = null)
        {
            var connString = this.dbHelper.ConnectString;
            var path = System.Text.RegularExpressions.Regex.Match(connString, @"[a-z,A-Z]\:\\.+\\").Value;
            if (path.IsNullOrEmpty())
            {
                path = System.Text.RegularExpressions.Regex.Match(connString, @"\/.+\/").Value;
            }
            if (!FileHelper.IsExistDirectory(path))
            {
                FileHelper.CreateDirectory(path);
            }
            this.Connection.Open();
            this.Connection.Close();
            return true;
        }
        public override List<DbColumnInfo> GetColumnInfosByTableName(string tableName, string dbName, bool isCache = true)
        {
            return GetColumnsByTableName(tableName);
        }
        public override bool AddRemark(DbTableInfo entity)
        {
            return true;
        }
        private List<DbColumnInfo> GetColumnsByTableName(string tableName)
        {
            tableName = GetTranslationTableName(tableName);
            string sql = "select * from " + tableName + " limit 0,1";
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            using (var reader = this.dbHelper.GetDataReader(sqle))
            {
                List<DbColumnInfo> result = new List<DbColumnInfo>();
                var schemaTable = reader.GetSchemaTable();
                foreach (DataRow row in schemaTable.Rows)
                {
                    DbColumnInfo column = new DbColumnInfo()
                    {
                        //TableName = tableName,
                        Type = row["DataTypeName"].ToString().Trim(),
                        IsNullable = (bool)row["AllowDBNull"],
                        IsIdentity = (bool)row["IsAutoIncrement"],
                        Description = null,
                        Name = row["ColumnName"].ToString(),
                        DefaultValue = row["defaultValue"].ToString(),
                        IsPrimaryKey = (bool)row["IsKey"],
                        MaxLength = Convert.ToInt32(row["ColumnSize"])
                    };
                    result.Add(column);
                }
                return result;
            }
        }
        public override bool BackupTable(string oldTableName, string newTableName, int maxBackupDataRows = int.MaxValue)
        {
            oldTableName = this.GetTranslationTableName(oldTableName);
            newTableName = this.GetTranslationTableName(newTableName);
            string sql = string.Format(this.BackupTableSql, newTableName, oldTableName, maxBackupDataRows);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        public override bool CreateTable(string tableName, List<DbColumnInfo> columns, bool isCreatePrimaryKey = true)
        {
            if (columns.HasValue())
            {
                foreach (var item in columns)
                {
                    //if (item.DbColumnName.Equals("GUID", StringComparison.CurrentCultureIgnoreCase))
                    //{
                    //    item.Length = 20;
                    //}
                    if (item.IsIdentity && !item.IsPrimaryKey)
                    {
                        item.IsPrimaryKey = true;
                        Check.Exception(item.Type == "integer", "Identity only integer type");
                    }
                }
            }
            string sql = GetCreateTableSql(tableName, columns);
            if (!isCreatePrimaryKey)
            {
                sql = sql.Replace("PRIMARY KEY AUTOINCREMENT", "").Replace("PRIMARY KEY", "");
            }
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        protected override string GetCreateTableSql(string tableName, List<DbColumnInfo> columns)
        {
            List<string> columnArray = new List<string>();
            Check.Exception(columns.IsNullOrEmpty(), "No columns found ");
            foreach (var item in columns)
            {
                string columnName = item.Name;
                string dataType = item.Type;
                if (dataType == "varchar" && item.MaxLength == 0)
                {
                    item.MaxLength = 1;
                }
                string dataSize = item.MaxLength > 0 ? string.Format("({0})", item.MaxLength) : null;
                string nullType = item.IsNullable ? this.CreateTableNull : CreateTableNotNull;
                string primaryKey = item.IsPrimaryKey ? this.CreateTablePirmaryKey : null;
                string identity = item.IsIdentity ? this.CreateTableIdentity : null;
                string addItem = string.Format(this.CreateTableColumn, this.GetTranslationColumnName(columnName), dataType, dataSize, nullType, primaryKey, identity);
                columnArray.Add(addItem);
            }
            string tableString = string.Format(this.CreateTableSql, this.GetTranslationTableName(tableName), string.Join(",\r\n", columnArray));
            tableString = tableString.Replace("`", "\"");
            return tableString;
        }
        public override bool IsAnyConstraint(string constraintName)
        {
            throw new NotSupportedException("Sqlite IsAnyConstraint NotSupportedException");
        }
        public override bool BackupDataBase(string databaseName, string fullFileName)
        {
            Check.ThrowNotSupportedException("Sqlite BackupDataBase NotSupported");
            return false;
        }
        #endregion
        #endregion
    }
}
